package com.study.lession.util.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * JDBC工具类
 * @author Administrator
 *
 */
public class JdbcUtil {
	
	/** 驱动名称 */
	private static String DRIVER;
	private static String USERNAME;
	private static String PASSWORD;
	private static String URL;
	
	private Connection connection;
	private ResultSet resultSet;
	private PreparedStatement preparedStatement;
	
	static {
		loadConfig();
	}
	
	/**
	 * 用来加载jdbc配置文件
	 */
	public static void loadConfig() {
		
		
		
		InputStream inputStream = JdbcUtil.class.getResourceAsStream("/jdbc.properties");
		Properties properties = new Properties();
		try {
			properties.load(inputStream);
			
			DRIVER = properties.getProperty("jdbc.driver","com.mysql.jdbc.Driver");
			USERNAME = properties.getProperty("jdbc.username");
			PASSWORD = properties.getProperty("jdbc.password");
			URL = properties.getProperty("jdbc.url");
			
		} catch (IOException e) {
			System.out.println("读取配置文件发生异常了。。。。。");
			e.printStackTrace();
		}
	}
	
		
	/**
	 * 获取数据库的连接
	 * @return
	 */
	public Connection getConnection() {

		try {
			Class.forName(DRIVER);
			connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (ClassNotFoundException e) {
			System.out.println("数据库连接获取失败....");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("数据库连接获取失败....");
			e.printStackTrace();
		}
		
		return connection;
	}
	
	/**
	 * 返回结果集
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException 
	 */
	public List<Map<String, Object>> findResultSet(String sql, List<?> params) throws SQLException {
		
		List<Map<String, Object>> list = new ArrayList<>();
		
 		preparedStatement = connection.prepareStatement(sql);
		if (null != params && params.size() > 0) {
			int index = 1;
			for (int i = 0; i < params.size(); i++) {
				preparedStatement.setObject(index++, params.get(i));
			}
		}
		
		resultSet = preparedStatement.executeQuery();
		
		ResultSetMetaData meta = resultSet.getMetaData();
		int length = meta.getColumnCount();
		
		while (resultSet.next()) {
			Map<String, Object> map = new HashMap<>();
			for (int i = 0; i < length; i++) {
				String columnName = meta.getColumnName(i + 1);
				Object columnValue = resultSet.getObject(columnName);
				if (null != columnValue) {
					map.put(columnName, columnValue);					
				}
			}
			list.add(map);
		}
		
		return list;
	}
	
	/**
	 * 增删改封装方法
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException 
	 */
	public int updateByPrepareStatement(String sql, List<?> params) throws SQLException {		
		preparedStatement = connection.prepareStatement(sql);
		if (null != params && params.size() > 0) {
			int index = 1;
			for (int i = 0; i < params.size(); i++) {
				preparedStatement.setObject(index++, params.get(i));
			}
		}
		
		return preparedStatement.executeUpdate();
	}
	
	/**
	 * 释放资源
	 */
	public void releaseConn() {
		if (null != resultSet) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if (null != preparedStatement) {
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if (null != connection) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
